# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html


# useful for handling different item types with a single interface

import logging
import pymysql as mysql
import crawler.util as util


class TestPipeline:

    def process_item(self, item, spider):
        # 数据预处理
        if 'a' in item:
            logging.info('a is in item')
        a = item['a']
        logging.info(f"a={a}")
        if a:
            logging.info("a is True")
        if a is None:
            logging.info("a is None")


class SpotPipeline:
    def __init__(self):
        self.sql = f"insert into `spot` (%s) values (%s);"

    def process_item(self, item, spider):
        db = spider.db
        # 数据预处理
        column = []
        value = []
        # url 去除多余斜杆
        spot_id = item['id']
        column.append('`spot_id`')
        value.append(f"{spot_id}")
        column.append('`cnname`')
        name = item['cnname'].replace("'", "\\'")
        value.append(f"'{name}'")

        if 'lat' in item and 'lng' in item:
            column.append('`lat`')
            value.append(f"{item['lat']}")
            column.append('`lng`')
            value.append(f"{item['lng']}")
        if 'url' in item:
            column.append('`url`')
            value.append(f"'{item['url']}'")
        if 'grade' in item:
            grade = float(item['grade'])
            column.append('`grade`')
            value.append("%.1f" % grade)
        if 'rank' in item:
            rank = int(item['rank'])
            column.append('`rank`')
            value.append(f"{rank}")

        try:
            # 执行sql语句
            sql = self.sql % (','.join(column), ','.join(value))
            db.cursor.execute(sql)
            # 提交到数据库执行
            db.db.commit()
            logging.info(f"数据插入, name={item['cnname']}, spot_id={spot_id}")
        except mysql.err.IntegrityError:
            db.db.rollback()
        except Exception as e:
            logging.error("数据插入错误, spider=%s,\n sql=%s \n error=%s" % (spider.name, sql, repr(e)))
            db.db.rollback()
        return item


class PositionPipeline:
    def __init__(self):
        self.sql = "update `spot` set\
                        `lat` = %f, `lng` = %f where `spot_id` = %d;"
        # print(self.sql)
        # print('启动pipeline')

    def __del__(self):
        try:
            self.db.close()
        except:
            pass

    def process_item(self, item, spider):
        db = spider.db
        # 数据预处理
        # url 去除多余斜杆
        spot_id = int(item['spot_id'])
        lat = float(item['lat'])
        lng = float(item['lng'])
        try:
            # 执行sql语句
            sql = self.sql % (lat, lng, spot_id)
            db.cursor.execute(sql)
            # 提交到数据库执行
            db.db.commit()
        except Exception as e:
            logging.error("数据更新错误, spider=%s,\n sql=%s, \n error=%s"\
                          % (spider, sql, repr(e)))
            db.db.rollback()
        return item


class CommentPipeline:
    def __init__(self):
        self.sql_comment = "insert into `comment` (%s) values (%s);"
        self.sql_user = "insert into `tourist` (%s) values (%s);"
        self.sql_finished = "update `spot` set `comment` = %d where `spot_id` = %d;"
        # print(self.sql)

    def process_item(self, item, spider):
        db = spider.db
        # 数据预处理
        if item.type == 'comment':
            # 查重
            if 'id' in item:
                sql = f"select `id` from `comment` where `comment_id`={item['id']};"
                db.cursor.execute(sql)
                res = db.cursor.fetchone()
                if res is not None:
                    # 重复
                    # logging.error(f"comment 重复, comment_id={item['id']}")
                    return

            column = []
            value = []

            column.append("`spot_id`")
            value.append(f"{int(item['spot_id'])}")

            column.append("`content`")
            content = item['content'].replace("'", "\\'")
            content = util.remove_emoji(content)
            value.append(f"'{content}'")

            column.append("`date`")
            # date = util.remove_whitespace(item['date'])
            value.append(f"'{item['date']}'")

            if 'id' in item:
                column.append("`comment_id`")
                value.append(f"{int(item['id'])}")
            if 'author_name' in item:
                column.append("`author_name`")
                author_name = item['author_name'].replace("'", "\\'")
                value.append(f"'{author_name}'")
            if 'author_id' in item:
                column.append("`author_id`")
                value.append(f"{int(item['author_id'])}")
            if 'star_level' in item:
                column.append("`star_level`")
                value.append(f"{float(item['star_level'])}")
            if 'img_num' in item:
                column.append("`img_num`")
                value.append(f"{int(item['img_num'])}")

            sql = self.sql_comment % (','.join(column), ','.join(value))
        elif item.type == 'tourist':
            # 查重
            if 'id' in item:
                sql = f"select `id` from `tourist` where `tourist_id`={item['id']};"
            elif 'name' in item:
                name = item['name'].replace("'", "\\'")
                name = util.remove_emoji(name)
                sql = f"select `id` from `tourist` where `name`='{name}';"
            else:
                return
            db.cursor.execute(sql)
            res = db.cursor.fetchone()
            if res is not None:
                # 重复
                return
            column = []
            value = []
            if 'id' in item:
                column.append("`id`")
                value.append(f"{item['id']}")
            if 'name' in item:
                column.append("`name`")
                name = item['name'].replace("'", "\\'")
                name = util.remove_emoji(name)
                value.append(f"'{name}'")
            if len(column) > 0:
                sql = self.sql_user % (','.join(column), ','.join(value))
            else:
                return
        elif item.type == 'finish_comment':
            spot_id = int(item['spot_id'])
            finished = int(item['finished'])
            sql = self.sql_finished % (finished, spot_id)
        else:
            return item
        # print(sql)
        try:
            # 执行sql语句
            db.cursor.execute(sql)
            # 提交到数据库执行
            db.db.commit()
        except mysql.err.IntegrityError:
            # logging.warning(f"重复插入,spider={spider.name}, \n sql={sql}")
            pass
        except mysql.err.DataError:
            logging.error(f'数据编码有误, \nsql={sql}')
        except Exception as e:
            logging.error(f"数据插入错误, spider={spider.name},\n sql={sql} \n error={repr(e)}")
            spider.crawler.engine.close_spider(spider, "错误")
            db.db.rollback()
        return item


class UserProfilePipeline:

    def __init__(self):
        self.sql = " update  `tourist` set %s where `tourist_id` = %d;"

    def process_item(self, item, spider):
        db = spider.db
        if 'id' not in item:
            return
        tourist_id = int(item['id'])
        if tourist_id == 0:
            return

        update = []
        if 'gender' in item:
            gender = item['gender']
            update.append(f"`gender`='{gender}'")
        if 'birthday' in item:
            birthday = item['birthday']
            update.append(f"`birthday`='{birthday}'")
        update.append(f"`_update`=True")
        sql = self.sql % (','.join(update), tourist_id)
        try:
            # 执行sql语句
            db.cursor.execute(sql)
            # 提交到数据库执行
            db.db.commit()
        except mysql.err.IntegrityError:
            # logging.warning(f"重复插入,spider={spider.name}, \n sql={sql}")
            db.db.rollback()
        except mysql.err.DataError:
            logging.error(f'数据有误, \nsql={sql}')
            pass
        except Exception as e:
            logging.error(f"数据插入错误, spider={spider.name},\n sql={sql} \n error=repr(e)")
            db.db.rollback()
        return item
